﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ClassModel;
using ClassCommon;
using System.Data.SqlClient;
using System.Data;

namespace ClassDAL
{
    public class t_class_schedule_DAL
    {

        /// <summary>
        /// 班级排课数据插入
        /// </summary>
        public static int InsertScheduleClasses(Dictionary<string, List<SchedulingClassModel>> dicts, int fileid)
        {
            // 备用数据
            // 老师集合
            var teachers = t_class_teacher_DAL.GetTeacherListWithFileId(fileid);
            // 班级集合
            var classes = t_class_class_DAL.GetClassListWithFileId(fileid);
            // 课程集合
            var courses = t_class_course_DAL.GetCourseListWithFileID(fileid);

            /**
             
             * 1. 遍历字典，拿到key == 拿到班级 == excel的每一行数据
             * 2. 根据key，拿到当前行的老师排课条件数据集合
             * 3. 从集合中，获取每一个scheduleModel 来取得，每一个老师在某个班每一个星期的排课条件，上课科目、老师姓名等信息。
             * 4. 对于，每一个班级有几门课，就有几个排课条件
             */

            // 全部的班级(当前年级)排课builder
            StringBuilder entireSQLBuilder = new StringBuilder();

            foreach (string className in dicts.Keys)
            {
                // 某一个班级的排课builder
                StringBuilder classSQLBuilder = new StringBuilder();
                // 拿到班级id
                int clsId = classes.Where(c => c.classname == className).FirstOrDefault().id;
                // 班级老师排课集合
                var scheduleList = dicts[className];
                foreach (SchedulingClassModel schedule in scheduleList)
                {
                    // 获取老师id
                    int tid = teachers.Where(t => t.teacher == schedule.teacherName).FirstOrDefault().id;
                    // 获取课程id
                    int cid = courses.Where(c => c.coursename == schedule.courseName).FirstOrDefault().id;
                    // fileid
                    // 条件都有了，可以看是拼接sql语句了。
                    // insert into t_class_schedule values(teacherid,classid,condition,fileid,couresied);
                    classSQLBuilder.AppendLine(string.Format(
                        "insert into t_class_schedule values({0},{1},'{2}',{3},{4});",
                            tid,
                            clsId,
                            schedule.condition,
                            fileid,
                            cid
                        ));

                }

                entireSQLBuilder.Append(classSQLBuilder.ToString());

            }



            // 插入数据
            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), System.Data.CommandType.Text, entireSQLBuilder.ToString());

        }

        /// <summary>
        /// 根据excel文件，获取班级的排课信息
        /// </summary>
        /// <param name="fileid"></param>
        public static List<t_class_schedule> GetScheduleDataWithFileId(int fileid)
        {
            string selectSQL = "select id,teacherid,classid,condition,fileid,courseid from t_class_schedule where fileid = @fileid order by classid asc;";

            SqlParameter selectParam = new SqlParameter()
            {
                ParameterName = "@fileid",
                Value = fileid
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam);
            if (ds == null || ds.Tables.Count == 0) return null;

            List<t_class_schedule> schedulesList = new List<t_class_schedule>();
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow row in rows)
            {
                t_class_schedule schedule = new t_class_schedule();
                schedule.id = Convert.ToInt32(row["id"]);
                schedule.teacherid = Convert.ToInt32(row["teacherid"]);
                schedule.condition = row["condition"].ToString();
                schedule.classid = Convert.ToInt32(row["classid"]);
                schedule.fileid = Convert.ToInt32(row["fileid"]);
                schedule.courseid = Convert.ToInt32(row["courseid"]);


                schedulesList.Add(schedule);
            }


            return schedulesList;
        }


        /// <summary>
        /// 获取每个班级的老师和课程信息
        /// </summary>
        /// <param name="classid">班级id</param>
        /// <param name="fileid">excel对应的fileid</param>
        public static List<t_class_Schedule_Bind> GetTeacherAndCourseInfo(int classid, int fileid)
        {
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.AppendLine("select tcs.*,tct.tearcher as teachername,tcc.coursename from ");
            selectBuilder.AppendLine("(select id, teacherid,classid,condition,fileid,courseid from t_class_schedule where classid = @classid and fileid = @fileid) tcs");
            selectBuilder.AppendLine("left join t_class_tearchers tct on tcs.teacherid = tct.id");
            selectBuilder.AppendLine("left join t_class_course tcc on tcs.courseid = tcc.id");

            SqlParameter[] selectParams = new SqlParameter[] 
            {
                new SqlParameter("@classid",classid),
                new SqlParameter("@fileid",fileid)
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, selectBuilder.ToString(), selectParams);
            if (ds == null || ds.Tables.Count == 0) return null;

            List<t_class_Schedule_Bind> resultList = new List<t_class_Schedule_Bind>();
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow row in rows)
            {
                t_class_Schedule_Bind tcsb = new t_class_Schedule_Bind();
                tcsb.id = Convert.ToInt32(row["id"]);
                tcsb.teacherid = Convert.ToInt32(row["teacherid"]);
                tcsb.classid = Convert.ToInt32(row["classid"]);
                tcsb.conditon = row["condition"].ToString();
                tcsb.flieId = Convert.ToInt32(row["fileid"]);
                tcsb.courseid = Convert.ToInt32(row["courseid"]);
                tcsb.teacherName = row["teachername"].ToString();
                tcsb.courseName = row["coursename"].ToString();

                resultList.Add(tcsb);
            }


            return resultList;

        }


        /// <summary>
        /// 根据课程id获取老师和班级信息
        /// </summary>
        /// <param name="courseid"></param>
        /// <param name="fileid"></param>
        public static List<t_class_Schedule_Bind> GetTeacherAndClassInfo(int courseid, int fileid)
        {
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.AppendLine("select tcs.*,tct.tearcher as teachername,tcc.coursename,tcc2.classname from ");
            selectBuilder.AppendLine("(select id, teacherid,classid,condition,fileid,courseid from t_class_schedule where courseid = @courseid and fileid = @fileid) tcs");
            selectBuilder.AppendLine("left join t_class_tearchers tct on tcs.teacherid = tct.id");
            selectBuilder.AppendLine("left join t_class_course tcc on tcs.courseid = tcc.id");
            selectBuilder.AppendLine("left join t_class_classes tcc2 on tcs.classid = tcc2.id;");

            SqlParameter[] selectParams = new SqlParameter[] 
            {
                new SqlParameter("@courseid",courseid),
                new SqlParameter("@fileid",fileid)
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, selectBuilder.ToString(), selectParams);

            if (ds == null || ds.Tables.Count == 0) return null;

            List<t_class_Schedule_Bind> resultList = new List<t_class_Schedule_Bind>();
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow row in rows)
            {
                t_class_Schedule_Bind tcsb = new t_class_Schedule_Bind();
                tcsb.id = Convert.ToInt32(row["id"]);
                tcsb.teacherid = Convert.ToInt32(row["teacherid"]);
                tcsb.classid = Convert.ToInt32(row["classid"]);
                tcsb.conditon = row["condition"].ToString();
                tcsb.flieId = Convert.ToInt32(row["fileid"]);
                tcsb.courseid = Convert.ToInt32(row["courseid"]);
                tcsb.teacherName = row["teachername"].ToString();
                tcsb.courseName = row["coursename"].ToString();
                tcsb.className = row["classname"].ToString();

                resultList.Add(tcsb);
            }


            return resultList;

        }

        /// <summary>
        /// 根据老师id获取老师一周上课的课程节次数
        /// </summary>
        /// <param name="teacherId"></param>
        /// <returns></returns>
        public static string GetTeacherClassesCount(int teacherId)
        {
            string selectSQL = "select condition from t_class_schedule where teacherid = @teacherid;";
            SqlParameter selectParam = new SqlParameter()
            {
                ParameterName = "@teacherid",
                Value = teacherId
            };

            string condition = SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam).ToString();

            return condition;
        }

        /// <summary>
        /// 根据fileid获取班级，根据班级在获取这个班一周要上几节课
        /// </summary>
        /// <param name="fileid"></param>
        /// <returns></returns>
        public static List<string> GetClassLessionCountInAWeek(int fileid)
        {
            string countSQL = "select condition from t_class_schedule where classid = (select min(id) from t_class_classes where fileId = @fileid);";

            SqlParameter countParam = new SqlParameter() 
            {
                ParameterName = "@fileid",
                Value = fileid
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, countSQL, countParam);
            if (ds == null || ds.Tables.Count == 0) return null;

            List<string> conditionsList = new List<string>();
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow row in rows)
            {
                conditionsList.Add(row["condition"].ToString());
            }


            return conditionsList;
            
        }
    }
}
